package org.openea.eap.module.obpm.util.custsql;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.openbpm.base.api.response.impl.ResultMsg;
import org.openbpm.base.core.id.IdUtil;
import org.openbpm.base.core.util.BeanUtils;
import org.openbpm.base.core.util.ExceptionUtil;
import org.openbpm.base.core.util.StringUtil;
import org.openea.eap.module.obpm.model.custsql.vo.SqlFieldsVO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Component
public class TableOperUtil {
    private Logger logger = LoggerFactory.getLogger(getClass());


    public ResultMsg<List<SqlFieldsVO>> getColumnModalBySql(String sql, String dsKey) {
        this.logger.info("根据sql脚本,数据源 获取字段列表设置,dsAlias:{},sql:{}", dsKey, sql);

        String tempTableName = "TEMP_TABLE_VIEW_SQL_" + IdUtil.getSuid();
        String dropViewSql = "drop table " + tempTableName;
        boolean isCreateTepTable = false;


        JdbcTemplate newJdbcTemplate = JdbcTemplateUtil.getJdbcTemplate(dsKey);


        try {
            sql = createTempTable(sql, dsKey, tempTableName, newJdbcTemplate);


            isCreateTepTable = true;


            Map<String, String> fieldCommentMap = getFieldCommentMap(tempTableName, newJdbcTemplate);


            List<SqlFieldsVO> list = getFieldList(sql, newJdbcTemplate, fieldCommentMap);


            return ResultMsg.SUCCESS(list);
        } catch (Exception e) {

            this.logger.error("通过sql获取字段列表失败,e:{}", ExceptionUtil.getRootErrorMseeage(e));


            if (isCreateTepTable) {
                newJdbcTemplate.execute(dropViewSql);
            }

            return ResultMsg.ERROR("通过sql获取字段列表失败:" + ExceptionUtil.getRootErrorMseeage(e));
        }
    }


    private List<SqlFieldsVO> getFieldList(String sql, JdbcTemplate newJdbcTemplate, Map<String, String> fieldCommentMap) {
        this.logger.info("通过sql获取字段列表,fieldCommentMap:{}", fieldCommentMap);

        SqlRowSet srs = newJdbcTemplate.queryForRowSet(sql);
        SqlRowSetMetaData srsmd = srs.getMetaData();
        List<SqlFieldsVO> list = new ArrayList<>();

        for (int i = 1; i < srsmd.getColumnCount() + 1; i++) {
            String cn = srsmd.getColumnName(i);
            String ctn = srsmd.getColumnTypeName(i);
            SqlFieldsVO field = new SqlFieldsVO();
            field.setName(cn);
            field.setAlign("c");
            field.setTableName(srsmd.getTableName(i));
            field.setExportAble(Integer.valueOf(1));
            field.setAddAble(Integer.valueOf(1));
            field.setEditAble(Integer.valueOf(1));
            field.setImportAble(Integer.valueOf(1));
            field.setSearchAble(Integer.valueOf(1));

            String columnLabel = srsmd.getColumnLabel(i);
            field.setFieldName(columnLabel);

            if (fieldCommentMap.containsKey(columnLabel)) {
                field.setFieldDesc(fieldCommentMap.get(columnLabel));
            } else {
                field.setFieldDesc(columnLabel);
            }


            String dataType = JdbcTemplateUtil.getDataType(ctn);
            field.setDataType(dataType);


            field.setHidden(Integer.valueOf(0));
            if (field.getFieldName().endsWith("id") || field.getFieldName().endsWith("Id")) {
                field.setHidden(Integer.valueOf(1));
            }
            field.setControlType(Integer.valueOf(1));


            if (dataType.equals("date")) {
                field.setControlType(Integer.valueOf(3));
                field.setDateFormat("yyyy-MM-dd");
            }


            if (dataType.equals("datetime")) {
                field.setControlType(Integer.valueOf(3));
                field.setDateFormat("yyyy-MM-dd HH:mm:ss");
            }

            field.setSn(Integer.valueOf(i));
            list.add(field);
        }
        return list;
    }


    private Map<String, String> getFieldCommentMap(String tempTableName, JdbcTemplate newJdbcTemplate) {
        this.logger.info("通过临时表 找到对应的字段属性,tempTableName:{}", tempTableName);

        String showColumnSql = "show full columns from " + tempTableName;
        List<Map<String, Object>> queryForList = newJdbcTemplate.queryForList(showColumnSql);

        JSONArray fieldCommentJa = JSONArray.parseArray(JSONArray.toJSONString(queryForList));

        Map<String, String> fieldCommentMap = new HashMap<>();
        for (int i = 0; i < fieldCommentJa.size(); i++) {
            JSONObject tempJo = fieldCommentJa.getJSONObject(i);
            if (BeanUtils.isNotEmpty(tempJo.get("Comment"))) {
                fieldCommentMap.put(tempJo.get("Field").toString(), tempJo.get("Comment").toString());
            }
        }
        return fieldCommentMap;
    }


    private String createTempTable(String sql, String dsAlias, String tempTableName, JdbcTemplate newJdbcTemplate) {
        sql = StringUtil.quotToStr(sql);
        sql = sql.replaceAll("#___#", "'");

        sql = SqlUtil.getNoDataSql(sql);
        String viewSql = "create temporary table " + tempTableName + " as " + sql;

        this.logger.info("创表脚本 , sql:{}", sql);


        newJdbcTemplate.execute(viewSql);

        this.logger.info("创表成功 ,dsAlias:{}", dsAlias);


        return sql;
    }
}
